﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data.Common;
using Excel = Microsoft.Office.Interop.Excel;


// Механизм по переносу подразделений перенести сюда
namespace DeloUtils.ImportDepartment
{
    class ImportDepartment
    {
        private static Dictionary<int, Int32> DepartmentList = new Dictionary<int, Int32>();
        private static Dictionary<Int32, string> DepartmentVertex = new Dictionary<Int32, string>();
        private static OleDbConnection connection;
        private static ListBox lsbDepartmentsList;
        public static string SqlPrefix;

        public static void Init(OleDbConnection conn, ListBox lsb, string Prefix)
        {
            connection = conn;
            lsbDepartmentsList = lsb;
            SqlPrefix = Prefix;
        }

        public static void InitListDepartment()
        {
            OleDbCommand cmdDepartment;
            if ((lsbDepartmentsList.Items.Count == 0) && (DepartmentVertex.Count == 0))
            {
                cmdDepartment = new OleDbCommand("SELECT ISN_NODE,CLASSIF_NAME FROM " + 
                    SqlPrefix + "DEPARTMENT WHERE (ISN_HIGH_NODE=0) AND (IS_NODE=0) ORDER BY WEIGHT", connection);
                OleDbDataReader drDepartment = cmdDepartment.ExecuteReader();
                int i;
                DepartmentList.Clear();
                while (drDepartment.Read())
                {
                    i = lsbDepartmentsList.Items.Add(drDepartment[1]);
                    DepartmentList.Add(i, Int32.Parse(drDepartment[0].ToString()));
                }
                drDepartment.Close();
            }
        }

        public static string AppendDepartment(Int32 ISN_HIGH, Int16 IS_NODE, String NAME,
            String SURNAME, String DUTY, Int16 DELETED, String DEP_INDEX, Int16 POST_H, String NOTES)
        {
            OleDbCommand ExecCmd = new OleDbCommand();
            ExecCmd.Connection = connection;
            ExecCmd.CommandType = CommandType.StoredProcedure;
            ExecCmd.CommandText = "GET_SEQ";
            ExecCmd.Parameters.Add("asname", OleDbType.VarChar);
            ExecCmd.Parameters["asname"].Value = "SEQ_CLASSIF";
            ExecCmd.Parameters.Add("airet", OleDbType.Integer);
            ExecCmd.Parameters["airet"].Direction = ParameterDirection.Output;
            ExecCmd.ExecuteNonQuery();
            Int32 ISN = Int32.Parse(ExecCmd.Parameters["airet"].Value.ToString());

            if (connection.Provider.ToLower().IndexOf("ora") > -1)
            {
                ExecCmd.CommandText = "SELECT DISTINCT Base36(" + ISN.ToString() + ") FROM WU";
            }
            else
            {
                ExecCmd.CommandText = "SELECT dbo.Base36(" + ISN.ToString() + ")";
            }
            ExecCmd.Parameters.Clear();
            ExecCmd.CommandType = CommandType.Text;
            OleDbDataReader dr = ExecCmd.ExecuteReader();
            if (!dr.Read()) return "";
            String DUE = dr[0].ToString();
            dr.Close();
            String DEPARTMENT_DUE = "0.";

            OleDbCommand cmdDepartment = new OleDbCommand();
            cmdDepartment.Connection = connection;
            cmdDepartment.CommandText = "select max(WEIGHT) FROM " + SqlPrefix + "DEPARTMENT WHERE ISN_HIGH_NODE=" + ISN_HIGH;
            object MAXWEIGHT = cmdDepartment.ExecuteScalar();
            Int32 _WEIGHT = 1;
            if ((MAXWEIGHT != null) && (MAXWEIGHT != DBNull.Value)) _WEIGHT = Int32.Parse(MAXWEIGHT.ToString()) + 1;
            cmdDepartment.CommandText = "SELECT DUE,LAYER FROM " + SqlPrefix + "DEPARTMENT WHERE ISN_NODE=" + ISN_HIGH;
            dr = cmdDepartment.ExecuteReader();
            Int32 _LAYER = 0;
            if (dr.Read())
            {
                DEPARTMENT_DUE = dr[0].ToString();
                DUE = DEPARTMENT_DUE + DUE;
                _LAYER = Int32.Parse(dr[1].ToString()) + 1;
            }
            dr.Close();
            DEPARTMENT_DUE = "0.";
            String SQLCOMMAND = "insert  into " + SqlPrefix + "department (DUE,ISN_NODE,ISN_HIGH_NODE,LAYER,IS_NODE,WEIGHT,MAXDUE,";
            SQLCOMMAND += "CLASSIF_NAME,SURNAME,DUTY,DEPARTMENT_DUE,PROTECTED,DELETED,";
            SQLCOMMAND += "DEPARTMENT_INDEX,POST_H,CARD_FLAG,CARD_NAME,NOTE";
            SQLCOMMAND += ") values ('" + DUE + "'," + ISN.ToString() + "," + ISN_HIGH.ToString() + "," + _LAYER + "," + IS_NODE.ToString() +
            "," + _WEIGHT.ToString() + ",'" + DUE + "','" + NAME + "','" + SURNAME + "','" + DUTY + "','" + DEPARTMENT_DUE +
            "',0," + DELETED.ToString() + ",'" + DEP_INDEX + "'," + POST_H.ToString() + ",0,null,'" + NOTES + "')";

            cmdDepartment.CommandText = SQLCOMMAND;
            cmdDepartment.ExecuteNonQuery();

            return ISN.ToString()+"#"+DUE;
        }
    }
}
